#!/usr/bin/env python 
# -*- coding: utf-8 -*- 
# @Time : 2018-06-13 9:59 
# @Author : Leo
# @File : Step5_ExportNewData.py 

"""
根据数据库中的batch导出本次更新过的字段
"""
import logging
import os
import shutil
import sqlite3
import time

from xjlibrary.database_moudel.simple.mysqlclient import MySqlDbConnect, SelctSqlFromDB

cur_path = os.path.dirname(os.path.realpath(__file__))

logging.basicConfig(level=logging.INFO,  # 最低输出
                    format='%(asctime)s %(filename)s[line:%(lineno)d] %(levelname)s %(message)s',
                    datefmt='%a, %d %b %Y %H:%M:%S')

dealSqlFieldsFunc = lambda text: "'" + text.replace("'", "''") + "'" if text is not None else 'null'


def MajorDbConnect():
    return MySqlDbConnect(cur_path, "db.ini")


def getNewDataFromDB(batch):
    """
    从总数据中提取更新字段
    :param conn: 数据库连接对象
    :param batch: 批次时间戳
    :return: 符合要求的结果生成器
    """
    Sql = "SELECT * FROM allfields WHERE batch >= %s" % batch
    conn = MajorDbConnect()
    logging.info('Start getting new data from MySQL.modify_title_info_zt...')
    result = SelctSqlFromDB(Sql, conn)
    result_gen = (item[:-2] for item in result)
    return result_gen


def dealItemTuple(item_tuple):
    item_list = [dealSqlFieldsFunc(field) for field in item_tuple]
    return tuple(item_list)


#
# def dealItemTuple2Str(item_tuple):
#     item_str = ','.join([dealSqlFieldsFunc(field) for field in item_tuple])
#     print(item_str)


def write2DB3(conn, items_list):
    """
    将符合要求的数据写入新的db3中
    :param conn: 数据库连接对象
    :param items_list: 字段列表
    :return: None
    """
    cursor = conn.cursor()
    Sql_temp = "INSERT OR IGNORE INTO modify_title_info_zt VALUES(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)"
    for item in items_list:
        item_tuple = dealItemTuple(item)
        Sql = Sql_temp % item_tuple
        print(Sql)
        cursor.execute(Sql)
    conn.commit()
    cursor.close()


def copyTempDB3(temp_path, copy_out):
    """
    拷贝创建新的db3文件
    :param temp_path: 模板db3文件路径
    :param copy_out: 新的db3输出路径
    :return: 新db3路径
    """
    today_str = str(getTodayDate())
    if not os.path.isfile(temp_path) or not os.path.isdir(copy_out):
        raise FileNotFoundError
    new_db3_name = 'tandfbook_' + today_str + '.db3'
    new_db3_path = os.path.join(copy_out, new_db3_name)
    logging.info('Copying DB3<%s ==> %s> file...' % (temp_path, new_db3_name))
    shutil.copyfile(temp_path, new_db3_path)
    logging.info('Copying files is done.')
    return new_db3_path


# 获取当天日期
def getTodayDate():
    return time.strftime('%Y%m%d', time.localtime(time.time()))


def start():
    temp_db3 = os.path.join(cur_path, "db3", "template_DB3.db3")
    new_db3_out = os.path.join(cur_path, "newDB3")
    new_db3_path = copyTempDB3(temp_db3, new_db3_out)
    logging.info('Start connecting to the DB3 database: %s' % new_db3_path)
    db3_conn = sqlite3.connect(new_db3_path)
    # logging.info('Start connecting to the MySQL database: %s' % db)

    new_items_gen = getNewDataFromDB(export_date)
    items_list = []
    for new_item in new_items_gen:
        items_list.append(new_item)
        if len(items_list) > 50:
            write2DB3(db3_conn, items_list)
            items_list.clear()
    write2DB3(db3_conn, items_list)
    logging.info('Generated new DB3 file has been completed')
    db3_conn.close()


if __name__ == '__main__':
    export_date = "2019101200"
    start()
